package com.hddf.project.service;

import java.io.OutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.hddf.project.base.BaseService;
import com.hddf.project.pojos.Comment;
import com.hddf.project.pojos.Project;
import com.hddf.project.pojos.ProjectPlan;
import com.hddf.project.util.O;

public class ExportExcelService extends BaseService {

	private static ExportExcelService ees = null;

	static {
		if (null == ees) {
			ees = new ExportExcelService();
		}
	}

	public static ExportExcelService getInstance() {
		return ees;
	}

	/**
	 * 根据计划ID导出Excel
	 */
	public void planExcelByPlanId(OutputStream os, ProjectPlan pp) throws Exception {
		HSSFWorkbook hwb = new HSSFWorkbook();// 创建EXCEL文件
		HSSFSheet sheet = hwb.createSheet();
		hwb.setSheetName(0, pp.getName() + "（" + pp.getCountProject() + "项）");
		sheet.createFreezePane(2, 2);// 冻结

		/**
		 * 每列宽度
		 */
		int sheetNum = 0;
		sheet.setColumnWidth(sheetNum, 2000);// 序号
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 7000);// 名称
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 7000);// 类别
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 4000);// 实施时间
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 4000);// 总投资
		sheetNum++;
		// sheet.setColumnWidth(sheetNum, 3840);// 提报单位
		// sheetNum++;
		sheet.setColumnWidth(sheetNum, 4000);// 主管部门
		sheetNum++;
		// sheet.setColumnWidth(sheetNum, 3840);// 牵头部门
		// sheetNum++;
		sheet.setColumnWidth(sheetNum, 4000);// 建设主体
		sheetNum++;
		// sheet.setColumnWidth(sheetNum, 3000);// 责任主体
		// sheetNum++;
		sheet.setColumnWidth(sheetNum, 3000);// 项目深度
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 10240);// 所在区县
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 10240);// 资金计划
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 10240);// 批示
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 10240);// 简介
		sheetNum++;
		sheet.setColumnWidth(sheetNum, 10240);// 备注

		int rowNum = 0;
		/**
		 * 表头设置和输出
		 */
		HSSFFont headfont = hwb.createFont();// 表头字体样式
		headfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
		headfont.setFontName("黑体");
		headfont.setFontHeightInPoints((short) 18);// 字号
		HSSFCellStyle headStyle = hwb.createCellStyle();// 表头样式
		headStyle.setFont(headfont);
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

		CellRangeAddress range = new CellRangeAddress(0, 0, 0, sheetNum);// 表头合并
		sheet.addMergedRegion(range);
		HSSFRow headRow = sheet.createRow(rowNum);// 表头行
		headRow.setHeight((short) 800);
		rowNum++;

		HSSFCell headCell = headRow.createCell(0);// 表头内容
		headCell.setCellValue(new HSSFRichTextString(pp.getName() + "（" + pp.getCountProject() + "项）"));
		headCell.setCellStyle(headStyle);

		/**
		 * 标题设置和输出
		 */
		HSSFFont titleFont = hwb.createFont();// 标题字体
		titleFont.setFontName("黑体");
		titleFont.setFontHeightInPoints((short) 14);// 字体大小
		HSSFCellStyle titleStyle = hwb.createCellStyle();// 标题样式
		titleStyle.setFont(titleFont);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

		HSSFRow titleRow = sheet.createRow(rowNum);// 标题行
		titleRow.setHeight((short) 600);
		rowNum++;

		int titleNum = 0;
		HSSFCell titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("序号");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("项目名称");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("项目类别");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("实施时间");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("总投资/亿元");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		// titleCell = titleRow.createCell(titleNum);// 标题内容
		// titleCell.setCellValue("提报单位");
		// titleCell.setCellStyle(titleStyle);
		// titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("主管部门");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		// titleCell = titleRow.createCell(titleNum);// 标题内容
		// titleCell.setCellValue("牵头部门");
		// titleCell.setCellStyle(titleStyle);
		// titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("建设主体");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		// titleCell = titleRow.createCell(titleNum);// 标题内容
		// titleCell.setCellValue("责任主体");
		// titleCell.setCellStyle(titleStyle);
		// titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("项目状态");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("项目地点");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("资金来源/亿元");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("领导批示");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("项目概况");
		titleCell.setCellStyle(titleStyle);
		titleNum++;

		titleCell = titleRow.createCell(titleNum);// 标题内容
		titleCell.setCellValue("形象进度及完成投资情况");
		titleCell.setCellStyle(titleStyle);

		/**
		 * 内容设置和输出
		 */
		HSSFFont contentFont = hwb.createFont();// 内容字体设置
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);// 字体大小
		HSSFCellStyle contentStyle = hwb.createCellStyle();
		contentStyle.setFont(contentFont);
		contentStyle.setWrapText(true);
		contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中

		int xuhao = 1;
		List<Project> pList = pp.getpList();
		for (int i = 0; i < pList.size(); i++) {
			Project p = pList.get(i);

			HSSFRow contentRow = sheet.createRow(rowNum);// 内容行
			contentRow.setHeight((short) 500);

			int contentNum = 0;
			HSSFCell content = contentRow.createCell(contentNum);// 序号
			content.setCellValue(xuhao);
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 项目名称
			content.setCellValue(p.getName());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 项目类别
			StringBuffer _type = new StringBuffer();
			_type.append(p.getFirstCategoryName());
			if (O.ckSt(p.getSecondCategoryName())) {
				_type.append("->" + p.getSecondCategoryName());
			}
			if (O.ckSt(p.getThirdCategoryName())) {
				_type.append("->" + p.getThirdCategoryName());
			}
			content.setCellValue(_type.toString());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 实施时间
			content.setCellValue(p.getImpl_time());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 总投资
			content.setCellValue(p.getFund() + "亿元");
			content.setCellStyle(contentStyle);
			contentNum++;

			// content = contentRow.createCell(contentNum);// 提报单位
			// content.setCellValue(p.getSubmit_side());
			// content.setCellStyle(contentStyle);
			// contentNum++;

			content = contentRow.createCell(contentNum);// 主管部门
			content.setCellValue(p.getMgr_depart());
			content.setCellStyle(contentStyle);
			contentNum++;

			// content = contentRow.createCell(contentNum);// 牵头部门
			// content.setCellValue(p.getLead_depart());
			// content.setCellStyle(contentStyle);
			// contentNum++;

			content = contentRow.createCell(contentNum);// 建设主体
			content.setCellValue(p.getBuild_side());
			content.setCellStyle(contentStyle);
			contentNum++;

			// content = contentRow.createCell(contentNum);// 责任主体
			// content.setCellValue(p.getDuty_side());
			// content.setCellStyle(contentStyle);
			// contentNum++;

			content = contentRow.createCell(contentNum);// 项目深度
			content.setCellValue(p.getPlan());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 所属区县
			content.setCellValue(p.getArea());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 资金计划
			content.setCellValue(p.getFund_plan());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 批示
			String comment = null;
			if (O.ckLt(p.getCommentList())) {
				for (Comment c : p.getCommentList()) {
					comment += "【" + c.getUserName() + "：" + c.getContent() + "】";
				}
			}
			content.setCellValue(comment);
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 简介
			content.setCellValue(p.getDescription());
			content.setCellStyle(contentStyle);
			contentNum++;

			content = contentRow.createCell(contentNum);// 备注
			content.setCellValue(p.getRemarks());
			content.setCellStyle(contentStyle);

			xuhao++;
			rowNum++;
		}
		hwb.write(os);
		os.flush();
		os.close();
	}
}